import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

import java.io.*;

public class DB {
  private Connection connection = null;
  private Statement statement = null;
  private ResultSet resultSet = null;

  public DB(String dbFilename) throws SQLException, ClassNotFoundException {
      // If the next statement fails with 'java.lang.ClassNotFoundException: org.sqlite.JDBC',
      // you need the jdbc driver.
      // It's in file sqlitejdbc-v056.jar, so add that to your classpath:
      //   java -cp 'sqlitejdbc-v056.jar:.' ...
      // (That driver is from http://www.zentus.com/sqlitejdbc/.)
      Class.forName("org.sqlite.JDBC");

      // Note: opening the connection to an sqlite3 db creates the db if it doesn't already exist,
      // so success at opening the db isn't enough (using sqlite) to tell if previously existed.
      // One way to deal with this is to just be sure that you've created and initialized the
      // db before running your code -- it's easiest to do that using a shell:
      //    $ sqlite3 <yourDBFileName>
      // For this sample, though, we want it to "just work," so we need to automate initialization.
      // We do that by presuming that if the db file exists, it means it has been initialized.

      boolean dbExists = new File(dbFilename).exists();

      // connect to the db
      connection = DriverManager.getConnection("jdbc:sqlite:hw4db.sqlite");
      statement = connection.createStatement();

      // if it didn't already exist, initialize it
      if ( !dbExists ) initializeDB();
  }

  /**
   * Close the DB (when done using it).
   */
  public void close() {
    try {
      statement.close();
      connection.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  /**
   * A method that returns the result of a SELECT query.
   */
  public ResultSet doQuery( String query ) throws SQLException {
    try {
      resultSet = statement.executeQuery( query );
    } catch (SQLException e) {
      e.printStackTrace();
      throw e;
    }
    return resultSet;
  }

  /**
   * A method to execute CREATE, DROP, and UPDATE queries.
   */
  public void doUpdate( String query ) throws SQLException {
    try {
      statement.executeUpdate( query );
    } catch (SQLException e) {
      e.printStackTrace();
      throw e;
    }
  }

  /**
   * a method to initialize the test/demo db.  You may, or may not, want to automate
   * db initialize (the alternative being creating the tables you need via command line
   * use of sqlite3).
   */
  private void initializeDB() {
    try {
      System.out.println("Initializing db...");

      // The id field is a unique identifier for a row.  It often turns out to be a good idea to have one,
      // even if you don't initially see a reason why it's required (as here).
      // For these tables, all fields (besides the key) are type text.
      doUpdate("CREATE TABLE images( id integer primary key, uid text, filename text)");
      doUpdate("INSERT INTO images(uid, filename) VALUES ('abcdefg', 'test1.jpg')");
      doUpdate("INSERT INTO images(uid, filename) VALUES ('012345-6789', 'test2.jpg')");

      doUpdate("CREATE TABLE comments( id integer primary key, uid text, comment text)");
      doUpdate("INSERT INTO comments(uid, comment) VALUES ('abcdefg', 'second comment')");
      doUpdate("INSERT INTO comments(uid, comment) VALUES ('012345-6789', 'other image comment')");

      // What follows is an example of:
      //   *  using int type in the db, for both Java int's and long's,
      //   *  using transactions (which have "all or nothing" behavior)

      doUpdate("CREATE TABLE timestamps(id integer primary key, ts integer, randomint int)");

      // First fill the just created table with some info, going out of our way to make it unsorted
      try {
        doUpdate("BEGIN TRANSACTION"); // all SQL operations up to the COMMIT or ROLLBACK either succeed or all fail
        
        for (int i = 0; i<10; i++) {
          doUpdate("INSERT INTO timestamps(ts, randomint) VALUES(" + System.currentTimeMillis() + ", " + i/2 + ")" );
        }
        for (int i = 9; i>=0; i--) {
          doUpdate("INSERT INTO timestamps(ts, randomint) VALUES(" + System.currentTimeMillis() + ", " + i/2 + ")" );
        }

        doUpdate("COMMIT");  // okay, make all updates since BEGIN TRANSACTION permanent
      } catch (Exception e) {
        doUpdate("ROLLBACK"); // throw away all updates since BEGIN TRANSACTION
        throw e;
      }

      // Now do it again, but causing an error during the process.  (You'll note that no records with
      // 2nd column in the 100's appear in the output produced by main().)
      try {
        doUpdate("BEGIN TRANSACTION");
        
        for (int i = 100; i<110; i++) {
          doUpdate("INSERT INTO timestamps(ts, randomint) VALUES(" + System.currentTimeMillis() + ", " + i/2 + ")" );
        }
        for (int i = 109; i>=100; i--) {
          doUpdate("INSERT INTO timestamps(ts, randomint) VALUES(" + System.currentTimeMillis() + ", " + i/2 + ")" );
        }

        doUpdate("BAD SQL STATEMENT");  // this will cause an error

        doUpdate("COMMIT");
      } catch (Exception e) {
        System.out.println("Caught intentional error.  Aborting transaction.\nNo records with 2nd column in the 100's will appear in output.\n");
        doUpdate("ROLLBACK");
      }

    } catch (Exception e) {
      e.printStackTrace();
    }
  }


  // main -- runs a little test/demo
  public static void main(String[] args) {
    DB db = null;
    String dbFilename = "hw4db.sqlite";

    try {
      db = new DB(dbFilename);

      ResultSet resultSet = db.doQuery("SELECT uid, filename FROM images");
      System.out.println("Images:");
      while (resultSet.next()) {
        System.out.println( "\t" + resultSet.getString("uid") + " " + resultSet.getString("filename") );
      }

      System.out.println("\nComments (result order sorted by image uid):");
      resultSet = db.doQuery("SELECT C.uid, C.comment FROM images I, comments C WHERE I.uid=C.uid ORDER BY I.uid");
      while (resultSet.next()) {
        System.out.println("\t" + resultSet.getString("uid") + "\t" + resultSet.getString("comment") );
      }

      db.doUpdate("UPDATE comments SET comment='new comment' WHERE uid='012345-6789'");


      System.out.println("\nUpdated comments:");
      resultSet = db.doQuery("SELECT C.uid, C.comment FROM images I, comments C WHERE I.uid=C.uid ORDER BY I.uid");
      while (resultSet.next()) {
        System.out.println("\t" + resultSet.getString("uid") + "\t" + resultSet.getString("comment") );
      }


      // two version of fetching from the timestamp/randomint table.
      // The first is unsorted, and fetches fields as though they were the types stated when
      // the table was created.
      System.out.println("\nFetching timestamps and random ints, unsorted");
      resultSet = db.doQuery("SELECT ts, randomint FROM timestamps");
      while (resultSet.next()) {
        System.out.println("\t" + resultSet.getLong("ts") + "\t" + resultSet.getInt("randomint") );
      }

      // The second version sorts the records, first by ts and then by randomint (if there are ts ties).
      // It also retrieves the timestamp as though it were a text field (which with sqlite3 it
      // basically is).
      System.out.println("\nFetching timestamps and random ints, in sorted order");
      resultSet = db.doQuery("SELECT ts, randomint FROM timestamps ORDER BY ts, randomint");
      while (resultSet.next()) {
        System.out.println("\t" + resultSet.getString("ts") + "\t" + resultSet.getInt("randomint") );
      }

      
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      if ( db != null ) {
        db.close();
        System.out.println("\nThe db is file " + dbFilename);
        System.out.println("Delete it before rerunning this script if you want to execute the db creation code again.");
        System.out.println("Don't, if not.");
      }
    }
  }
}